package com.elitel.common.businessJdbc;

import com.elitel.common.businessJdbc.mongodb.MongoSelectStatement;
import com.elitel.common.businessJdbc.mysql.MysqlSelectPreparedStatement;
import com.elitel.common.businessJdbc.oracle.OracleSelectPreparedStatement;
import com.elitel.common.businessJdbc.postgre.PostgreSQLSelectPreparedStatement;
import com.elitel.common.businessJdbc.sqlserver.SqlServerSelectPreparedStatement;
import com.elitel.common.utils.XCommonUtils;
import com.elitel.frame.main.entity.CfgDb;
import com.elitel.frame.main.entity.access.FilterSqlHandlerDTO;
import com.elitel.frame.main.entity.constant.DictionaryConstant;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;

import java.time.Year;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

// here is for store condition for preparedstatement
// and handle custom syntax for freemaker without handle syntax
@Data
@Slf4j
public abstract class BusinessSelectPreparedStatement extends AbstractBusinessPreparedStatement{

    // here is provider fields section
    protected String fieldSection;
    protected String tableSection;
    protected String whereSql;
    protected Map<String, Object> conditionSection = new LinkedHashMap<String, Object>();
    protected Map<String, String> fieldTypeMap = new HashMap<String, String>();
    protected Map<String, String> fieldConditionSymbol = new HashMap<String, String>();
    protected String orderSection;
    protected Map<String, Object> passCondition;
    // pageable
    private boolean isPageable;
    protected int pageNumber = 1;
    protected int pageSize = 1000;

    // add cfgDB for mongodb
    protected CfgDb cfgDb;
    /**过滤数据SQL处理对象*/
    protected FilterSqlHandlerDTO filterSqlHandlerDTO;

    public static Builder builder(){
        return new Builder(DictionaryConstant.DatabaseType_MYSQL);
    }

    public static Builder builder(String databaseType){
        return new Builder(databaseType);
    }

    public String getPrecompileCountSql(){
        // useing mysql Test
        StringBuilder sb = new StringBuilder();
        sb.append("select count(*) from ( ")
            .append(sqlTemplate())
            .append(" ) a");
        return sb.toString();
    }

    // here is general sql template
    public String sqlTemplate(){
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT ")
            .append(getFieldSection())
            .append(" FROM ")
            .append(getTableSection())
            .append(" WHERE 1= 1 ")
            .append(getWhereSql())
            .append(containPlaceHolderCondition());
        return sb.toString();
    }

    protected String containPlaceHolderCondition(){
        if(conditionSection.isEmpty())
            return "";

        StringBuilder sb = new StringBuilder();
        for(Map.Entry<String, Object> conditionItem : conditionSection.entrySet()){
            sb.append(" AND ")
                .append(conditionItem.getKey())
                .append(fieldConditionSymbol.get(conditionItem.getKey()))
                .append(" ? ");// here is for PreparedStatement symtax
        }

        return sb.toString();
    }

    public Map<String, Object> getConditionSection(){
        return conditionSection;
    }

    private void putConditionItem(String key, Object value){
        conditionSection.put(key, value);
    }

    public static class Builder{
        private String serviceGuid;
        private String databaseType;
        private String fieldSection;
        private String tableSection;
        private String conditionSection;
        private String whereSql;
        private String orderSection;
        private Map<String, Object> passCondition;
        private Map<String, String> fieldTypeMap;
        private boolean isPageable;
        private int pageNumber;
        private int pageSize;
        private CfgDb cfgDb;
        private FilterSqlHandlerDTO filterSqlHandlerDTO;

        public Builder(String databaseType){
            this.databaseType = databaseType;
        }

        public Builder serviceGuid(String serviceGuid){
            this.serviceGuid = serviceGuid;
            return this;
        }

        public Builder fieldSection(String fieldSection){
            this.fieldSection = fieldSection;
            return this;
        }

        public Builder tableSection(String tableSection){
            this.tableSection = tableSection;
            return this;
        }

        public Builder conditionSection(String conditionSection){
            this.conditionSection = conditionSection;
            return this;
        }

        public Builder whereSql(String whereSql){
            this.whereSql = whereSql;
            return this;
        }

        public Builder orderSection(String orderSection){
            this.orderSection = orderSection;
            return this;
        }

        public Builder fieldTypeMap(Map<String,String> fieldTypeMap){
            this.fieldTypeMap = fieldTypeMap;
            return this;
        }

        public Builder cfgDb(CfgDb cfgDb){
            this.cfgDb = cfgDb;
            return this;
        }

        public Builder filterSqlHandler(FilterSqlHandlerDTO filterSqlHandlerDTO){
            this.filterSqlHandlerDTO = filterSqlHandlerDTO;
            return this;
        }

        public Builder passCondition(Map<String, Object> passCondition){
            if(passCondition == null)
                this.passCondition = new HashMap<>();
            else
                this.passCondition = passCondition;

            return this;
        }

        public Builder isPageable(boolean isPageable){
            this.isPageable = isPageable;
//            this.isPageable = true;


            return this;
        }

        public Builder pageNumber(String pageNo){
            int pageNumber;
            try{
                pageNumber = Integer.parseInt(pageNo);

            }catch(Exception e){
                pageNumber = 1;
            }
            this.pageNumber = pageNumber;
            return this;
        }

        public Builder pageSize(String strPageSize){
            int pageSize;
            try{
                pageSize = Integer.parseInt(strPageSize);
            }catch(Exception e){
                pageSize = 10;
            }
            this.pageSize = pageSize;
//            this.pageSize = 1000;

            return this;
        }

        public BusinessSelectPreparedStatement build(){
            BusinessSelectPreparedStatement bps = createBusinessPreparedStatementByDatabaseType();
            bps.setServiceGuid(serviceGuid);
            bps.setDatabaseType(databaseType);
            bps.setFieldSection(fieldSection);
            bps.setTableSection(tableSection);
            bps.setWhereSql(whereSql);
            bps.setOrderSection(orderSection);
            bps.setFieldTypeMap(fieldTypeMap);
            bps.setPassCondition(passCondition);
            bps.setCfgDb(cfgDb);
            bps.setPageable(isPageable);
            bps.setFilterSqlHandlerDTO(filterSqlHandlerDTO);
            if(bps.isPageable()){
                bps.setPageNumber(pageNumber);
                bps.setPageSize(pageSize);
            }
            if (conditionSection.contains("{{{")&&conditionSection.contains("}}}")) {
                // here handle condition section
                // condition may 0 1,or more element,
                String[] conditionItems = conditionSection.split("and");

                // split existed , may existed ""
                // how to split condition item , first use equals symbol to Test

                for(String conditionItem : conditionItems){
                    if(XCommonUtils.isEmpty(conditionItem) || StringUtils.isBlank(conditionItem))
                        continue;

                    // here is split symtax , need check ,it's not equals =
                    // before parse the function in column Alias feature to parse
                    String conditionSymbol = XCommonUtils.findConditionSymbol(conditionItem);
                    if(XCommonUtils.isEmpty(conditionSymbol)){
                        log.error("Can't find conditionSymbol : " + conditionSymbol);
                        continue;
                    }

                    String[] keyValue = conditionItem.split(conditionSymbol);
                    if(keyValue.length != 2)
                        continue;

                    // here first suppose open symbol {{  close symbol }}
                    String columnName = keyValue[0].trim();
                    String needParseValue = keyValue[1].trim();
                    Object value ;
                    String freemakerSyntaxKey = "-1";
                    if(needParseValue.contains(XCommonUtils.OpenToken) && needParseValue.contains(XCommonUtils.CloseToken)){
                        // here need to parse placeHolder by using
                        freemakerSyntaxKey = needParseValue.replace(XCommonUtils.OpenToken, "").replace(XCommonUtils.CloseToken, "");
                        value = passCondition.get(freemakerSyntaxKey);
                    }else{
                        value = needParseValue;
                    }

                    // here is handle value format to real data type
                    String fieldType = fieldTypeMap.get(freemakerSyntaxKey);
                    value = autoHandleDateRelativeType(bps, value, fieldType);

                    // here need to record conditon symbol for construct sql statement
                    bps.getFieldConditionSymbol().put(columnName, conditionSymbol);
                    bps.putConditionItem(columnName, value);
                }
            }


            return bps;
        }

        private Object autoHandleDateRelativeType(BusinessSelectPreparedStatement bps, Object value, String fieldType) {
            if(XCommonUtils.isNotEmpty(fieldType)){
                if(XCommonUtils.isContainLeastOneString(fieldType, "date","datetime","timestamp")){
                    value = autoHandleDateTimeType(bps.getServiceGuid(), value, fieldType);
                }
            }
            return value;
        }

        private Object autoHandleDateTimeType(String serviceGuid, Object value, String fieldType) {
            if(fieldType.length() == 10 || fieldType.length() == 13){
                // Try convert date from timestamp
                Long timestamp = null;
                try{
                    timestamp = Long.parseLong(fieldType);
                }catch(Exception e){
                }
                if(timestamp != null){
                    value = new Date(timestamp);
                }
            }

            if(!(value instanceof Date)){
                // auto parse date type
                String dateTime = value.toString().trim();

                // here is add current year
                // here is contain /
                if(dateTime.contains("/")){
                    dateTime = dateTime.replaceAll("/", "-");
                }
                // digit 5: 12-31
                if(dateTime.length() == 5){
                    Year year = Year.now();
                    dateTime = year.toString() + "-" + dateTime;
                }
                // digit 10: 2022-12-31
                if(dateTime.length() == 10){
                    dateTime += " 00:00:00.000";
                }
                // digit 16: 2022-12-32 10:37
                if(dateTime.length() == 16){
                    dateTime += ":00.000";
                }
                // digit 19: 2022-12-31 10:37:00
                if(dateTime.length() == 19){
                    dateTime += ".000";
                }
                // digit 23: 2022-12-31 10:37:00.000
                Date formatDateTime = XCommonUtils.parseChineseDateToMillisecond(dateTime);
                if(formatDateTime == null){
                    log.error("serviceGuid:"+ serviceGuid + " parse date type is fail , value =" + value + " final convert dateTime: " + dateTime);
                }else{
                    value = formatDateTime;
                }

            }
            return value;
        }

        private BusinessSelectPreparedStatement createBusinessPreparedStatementByDatabaseType() {
            BusinessSelectPreparedStatement bps;
            switch(databaseType){
                case DictionaryConstant.DatabaseType_MYSQL:
                    bps = new MysqlSelectPreparedStatement();
                    break;
                case DictionaryConstant.DatabaseType_POSTGRESQL:
                    bps = new PostgreSQLSelectPreparedStatement();
                    break;
                case DictionaryConstant.DatabaseType_ORACLE:
                    bps = new OracleSelectPreparedStatement();
                    break;
                case DictionaryConstant.DatabaseType_SQLSERVER:
                    bps = new SqlServerSelectPreparedStatement();
                    break;
                case DictionaryConstant.DatabaseType_MONGODB:
                    bps = new MongoSelectStatement();
                    break;
                default:
                    bps = new DefaultSelectPreparedStatement();
            }
            return bps;
        }
    }

}
